package com.sjsu.CMPE226.shard.hibernate.app;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

import com.sjsu.CMPE226.Hibernate.config.LocationHash;
import com.sjsu.CMPE226.shard.hibernate.data.Observation;

public class ShardedApp {
	protected LocationHash locator;
	SimpleDateFormat sdf = null;
	Date dateObs = null;

	public ShardedApp() {
		locator = new LocationHash(2);
	}

	@SuppressWarnings("unchecked")
	public List<Observation> findAllObservationsForDate() {
		sdf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			dateObs = sdf.parse("2012-10-03");
		} catch (ParseException e1) {
			e1.printStackTrace();
		}
		List<Observation> obs = null;
		Session session = locator.locate(dateObs);
		try {
			session.beginTransaction();
			Query query = session
					.createQuery("From Observation o where o.observationsDateTime = '2012-10-01'");
			obs = query.list();
			session.getTransaction().commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}
		// }

		return obs;
	}

	@SuppressWarnings("unchecked")
	public List<Observation> findAllObservationsForState() {
		List<Observation> obs = null;
		sdf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			dateObs = sdf.parse("2012-10-03");
		} catch (ParseException e1) {
			e1.printStackTrace();
		}
		Session session = locator.locate(dateObs);
		try {
			session.beginTransaction();
			SQLQuery query = session
					.createSQLQuery("Select S.Station_Id as SID, S.Station_Name as SN, "
							+ "O.* , max(O.Temperature) as MT, "
							+ "min(O.Temperature) as MnT, max(O.Relative_Humidity) as MR, "
							+ "min(O.Relative_Humidity) as MnR, max(O.Dew_Point) as MD, "
							+ "min(O.Dew_Point) as MnD, max(O.Gust) as MG, O.P24I as p24i, S.Elevation as Ele from "
							+ "observations O, stations_info S "
							+ "where S.Station_Id = O.Station_Id "
							+ "and S.State = 'CA' group by S.Station_Name");
			obs = query.list();
			session.getTransaction().commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}

		return obs;
	}

	@SuppressWarnings("unchecked")
	public List<Observation> findAllObservationsForNetwork() {
		List<Observation> obs = null;
		sdf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			dateObs = sdf.parse("2012-10-03");
		} catch (ParseException e1) {
			e1.printStackTrace();
		}
		Session session = locator.locate(dateObs);
		try {
			session.beginTransaction();
			SQLQuery query = session
					.createSQLQuery("Select S.Station_Id SID, S.Station_Name SN, S.Network_Id NN, "
							+ "O.*, max(O.Temperature) MT, "
							+ "min(O.Temperature) MnT, max(O.Relative_Humidity) MR, "
							+ "min(O.Relative_Humidity) MnR, max(O.Dew_Point) MD, "
							+ "min(O.Dew_Point) MnD, max(O.Gust) MG, O.P24I p24i, S.Elevation Ele from "
							+ "observations O, stations_info S "
							+ "where S.Station_Id = O.Station_Id "
							+ "and S.Network_Id = 65 "
							+ "group by S.Station_Name ");
			obs = query.list();
			session.getTransaction().commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}

		return obs;
	}

	@SuppressWarnings("unchecked")
	public List<Observation> findAllObservationsForNetworkAndProvider() {
		List<Observation> obs = null;
		sdf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			dateObs = sdf.parse("2012-10-03");
		} catch (ParseException e1) {
			e1.printStackTrace();
		}
		Session session = locator.locate(dateObs);
		try {
			session.beginTransaction();
			SQLQuery query = session
					.createSQLQuery("Select S.Station_Id SID, S.Station_Name SN, "
							+ "S.Network_Id NN, S.Primary_Provider_Id PID, O.*, max(O.Temperature) MT, "
							+ "min(O.Temperature) MnT, max(O.Relative_Humidity) MR, "
							+ "min(O.Relative_Humidity) MnR, max(O.Dew_Point) MD, "
							+ "min(O.Dew_Point) MnD, max(O.Gust) MG, O.P24I p24i, S.Elevation Ele from "
							+ "observations O, stations_info S where S.Station_Id = O.Station_Id "
							+ "and S.Primary_Provider_Id = 2 "
							+ "and S.Network_Id = 1 "
							+ "group by S.Station_Name ");
			obs = query.list();
			session.getTransaction().commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}

		return obs;
	}

	public int updateStationsForSecondaryStation() {
		int updatedRows = 0;
		sdf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			dateObs = sdf.parse("2012-10-03");
		} catch (ParseException e1) {
			e1.printStackTrace();
		}
		Session session = locator.locate(dateObs);
		try {
			session.beginTransaction();
			Query query = session.createQuery("update Station s "
					+ "set s.elevation = 15 " + "where s.elevation = 0");
			updatedRows = query.executeUpdate();
			session.getTransaction().commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}

		return updatedRows;
	}

	public int updateObservationsForSeaLevelPressure() {
		int updatedRows = 0;
		sdf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			dateObs = sdf.parse("2012-10-03");
		} catch (ParseException e1) {
			e1.printStackTrace();
		}
		Session session = locator.locate(dateObs);
		try {
			session.beginTransaction();
			SQLQuery query = session
					.createSQLQuery("update observations o, stations_info s "
							+ "set o.Sea_Level_Pressure = 0 "
							+ "where s.State = 'VA' and "
							+ "o.Sea_Level_Pressure = -9999 and o.Station_Id = s.Station_Id");
			updatedRows = query.executeUpdate();
			session.getTransaction().commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}

		return updatedRows;
	}

	public int deleteStationsForLatitude() {
		int deletedRows = 0;
		sdf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			dateObs = sdf.parse("2012-10-03");
		} catch (ParseException e1) {
			e1.printStackTrace();
		}
		Session session = locator.locate(dateObs);
		try {
			session.beginTransaction();
			Query query = session
					.createQuery("Delete from Station s where s.latitude > 60");
			deletedRows = query.executeUpdate();
			session.getTransaction().commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}

		return deletedRows;
	}

	public int deleteObservationsForAltimeter() {
		int deletedRows = 0;
		sdf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			dateObs = sdf.parse("2012-10-03");
		} catch (ParseException e1) {
			e1.printStackTrace();
		}
		Session session = locator.locate(dateObs);
		try {
			session.beginTransaction();
			SQLQuery query = session
					.createSQLQuery("Delete from observations USING observations INNER JOIN stations_info "
							+ "ON observations.Station_Id = stations_info.Station_Id where "
							+ "stations_info.Station_Name like 'CW%' and observations.Altimeter = -9999");
			deletedRows = query.executeUpdate();
			session.getTransaction().commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}

		return deletedRows;
	}

}
